package com.jxc.service.impl;

import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.FileItemFactory;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.jfree.chart.ChartFactory;
import org.jfree.chart.JFreeChart;
import org.jfree.data.general.DefaultPieDataset;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
 
import com.jxc.dao.BaseDaoI;
import com.jxc.model.po.CustomerType;
import com.jxc.model.po.Member;
import com.jxc.model.po.MemberType;
import com.jxc.model.po.Repertory;
import com.jxc.model.po.User;
import com.jxc.model.vo.DataGrid;
import com.jxc.model.vo.VMember;
import com.jxc.model.vo.VSalelist;
import com.jxc.service.MemberServiceI;

@Service("memberService")
public class MemberServiceImpl implements MemberServiceI {
	@Autowired
	private BaseDaoI<Member> memberDao;

	@Override
	public List<VMember> combobox(VMember vmember) { 
		return this.changeModel(this.findCombobox(vmember));
	}

	@Override
	public DataGrid datagrid(VMember vMember) {
		DataGrid j = new DataGrid();
		try{
			j.setRows(this.changeModel(this.find(vMember)));
			j.setTotal(this.total(vMember));
		}catch(Exception e){
			e.printStackTrace();
		}
		return j;
	}

	public DataGrid datagridTotal(VMember vMember) {
		// TODO Auto-generated method stub
		DataGrid j = new DataGrid();
		List<Object[]> vms = memberTotal(vMember);

		j.setRows(this.changeModelTotal(vms,vMember));
		//j.setTotal(this.totalTotal(vMember));
		return j;
	}
	private List<Member> findCombobox(VMember vMember) {
		String hql = "select t from Member t where t.isDeleted=false ";

		List<Object> values = new ArrayList<Object>();
		hql = addWhereCombobox(vMember, hql, values);

		if (vMember.getSort() != null && vMember.getOrder() != null) {
			hql += " order by " + vMember.getSort() + " " + vMember.getOrder();
		}
		return memberDao.find(hql, values);
	}
	private String addWhereCombobox(VMember vMember, String hql,
			List<Object> values) {
		/*if(vMember.getMobile()!= null&&vMember.getMobile().trim()!=null&&!vMember.getMobile().trim().equals("")){
			String aa = String.valueOf(vMember.getMobile().trim().charAt(0));
			if(aa=="1"||aa.equals("1")||aa=="0"||aa.equals("0")){
				hql += " and t.mobile = ?";
				values.add(vMember.getMobile());
			}else{
				hql += " and t.serial = ?";
				values.add(vMember.getMobile());
			}
		}*/
		if(vMember.getMobile()!=null){
			hql += " and ( t.mobile = ? or t.serial = ? ) ";
			values.add(vMember.getMobile());
			values.add(vMember.getMobile());
		}
		if(vMember.getSerial()!= null&&vMember.getSerial().trim()!=null&&!vMember.getSerial().trim().equals("")){
			hql += " and t.serial = ?";
			values.add(vMember.getSerial());
		}
		if(vMember.getId()!= null&&!vMember.getId().equals("")){
			hql += " and t.id = ?";
			values.add(vMember.getId());
		}if(vMember.getUid()!=null){
			hql += " and t.user.id = ?";
			values.add(vMember.getUid());
		}
		return hql;
	}

	private List<Member> find(VMember vMember) {
		String hql = "from Member t where t.isDeleted=false ";

		List<Object> values = new ArrayList<Object>();
		hql = addWhere(vMember, hql, values);

		if (vMember.getSort() != null && vMember.getOrder() != null) {
			hql += " order by " + vMember.getSort() + " " + vMember.getOrder();
		} 
		return memberDao.find(hql, values, vMember.getPage(), vMember.getRows());
	}
	private List<Member> findTotal(VMember vMember) {
		String hql = "from Member t where t.isDeleted=false ";
		List<Object> values = new ArrayList<Object>();
		hql = addWhereTotal(vMember, hql, values);

		if (vMember.getSort() != null && vMember.getOrder() != null) {
			hql += " order by " + vMember.getSort() + " " + vMember.getOrder();
		}
		return memberDao.find(hql, values, vMember.getPage(), vMember.getRows());
	}
	
	private Long totalTotal(VMember vMember) {
		String hql = "select count(*) from Member t where t.isDeleted=false ";
		List<Object> values = new ArrayList<Object>();
		hql = addWhere(vMember, hql, values);
		return memberDao.count(hql, values);
	}
	private List<VMember> changeModel(List<Member> members) {
		List<VMember> vMemberes = new ArrayList<VMember>();
		if (members != null && members.size() > 0) {
			for (Member m : members) {
				VMember vMember = new VMember();
				BeanUtils.copyProperties(m, vMember);
				if (m.getMemberType() != null) {
					MemberType type = m.getMemberType();
					vMember.setTid(type.getId());
					vMember.setTname(type.getName());
					if(m.getMemberType().getDiscount()!=null){
						vMember.setDiscountNum(m.getMemberType().getDiscount().getDiscountNum());
					}
				}
				/*if (m.getCustomerType() != null) {
					CustomerType ct = m.getCustomerType();
					vMember.setCtId(ct.getId());
					vMember.setCtName(ct.getName());
				}*/
				if (m.getUser() != null) {
					User user = m.getUser();
					vMember.setUid(user.getId());
					vMember.setUname(user.getUsername());
					vMember.setUpusertype(user.getUserType());
				}
				
				if(m.getRepertory()!=null){
					vMember.setRepertoryName(m.getRepertory().getName());
					vMember.setRid(m.getRepertory().getId());
				}
				
				vMemberes.add(vMember);
			}
		}
		return vMemberes;
	}
	
	/**
	 * 把List<object[]>类型的会员统计数据封装成对象用于datagrid显示
	 * @param members
	 * @param vMember
	 * @return
	 */
	private List<VMember> changeModelTotal(List<Object[]> members,VMember vMember) {
		List<VMember> vMemberes = new ArrayList<VMember>();
		try{
			if (members != null && members.size() > 0) {
				for (int i = 0 ; i < members.size();i++) {
					VMember vm = new VMember();
					if(vMember.getGroupby()!=null&&vMember.getGroupby().trim().equals("sex")){
						vm.setSex((String)(members.get(i)[0]));
					}else if(vMember.getGroupby()!=null&&vMember.getGroupby().trim().equals("repertory")){
						vm.setRname((String)(members.get(i)[0]));
					}else if(vMember.getGroupby()!=null&&vMember.getGroupby().trim().equals("type")){
						vm.setMtname((String)(members.get(i)[0]));
					}else{
						vm.setRname((String)(members.get(i)[0]));
					}
					vm.setConsume(Double.parseDouble(String.valueOf(members.get(i)[1])));
					vm.setPoint(Integer.parseInt(String.valueOf(members.get(i)[2])));
					vm.setNum(Integer.parseInt(String.valueOf(members.get(i)[3])));
					vMemberes.add(vm);
				}
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		
		return vMemberes;
	}

	private Long total(VMember vMember) {
		String hql = "select count(*) from Member t where t.isDeleted=false ";
		List<Object> values = new ArrayList<Object>();
		hql = addWhere(vMember, hql, values);
		return memberDao.count(hql, values);
	}

	//统一去掉左边的like
	private String addWhere(VMember vMember, String hql, List<Object> values) {
		if (vMember.getName() != null) {
			hql += " and t.name like ? ";
			values.add((vMember.getName()).trim()+"%%");
		}
		if (vMember.getMobile() != null&&!vMember.getMobile().equals("")) {
			hql += " and t.mobile like ? ";
			values.add(vMember.getMobile().trim()+"%%");
		}
		if (vMember.getUid() != null&&!vMember.getUid().equals("")) {
			hql += " and t.user.id = ? ";
			values.add(vMember.getUid());
		}
		if (vMember.getConsumeStart() != null&&!vMember.getConsumeStart().equals("")) {
			hql += " and t.consume >= ?";
			values.add(vMember.getConsumeStart()); 
		}
		if (vMember.getConsumeEnd() != null&&!vMember.getConsumeEnd().equals("")) {
			hql += "and t.consume <= ?";
			values.add(vMember.getConsumeEnd());
		} 
		if (vMember.getTid() != null) {
			hql += "and t.memberType.id = ?";
			values.add(vMember.getTid());
		} 
		
		if(vMember.getRid()!=null){
			hql+=" and t.repertory.id = ?";
			values.add(vMember.getRid());
		}
		
		return hql;
	}
	
	private String addWhereTotal(VMember vMember, String hql, List<Object> values) {
		if (vMember.getUid() != null) {
			hql += " and t.user.id = ? ";
			values.add(vMember.getUid());
		}
		if (vMember.getConsumeStart() != null && vMember.getConsumeEnd() != null) {
			hql += " and t.consume >= ? and t.consume <= ? ";
			values.add(vMember.getConsumeStart());
			values.add(vMember.getConsumeEnd());
		}

		if (vMember.getPointStart() != null && vMember.getPointEnd() != null) {
			hql += " and t.point >= ? and t.point <= ? ";
			values.add(vMember.getPointStart());
			values.add(vMember.getPointEnd());
		}		
		return hql;
	}

	@Override
	public void delete(String ids) {
		if (ids != null) {
			for (String id : ids.split(",")) {
				if (!id.trim().equals("0")) {
					Member m = memberDao.get(Member.class, Long.valueOf(id.trim()));
					if (m != null) {
						m.setIsDeleted(true);
						memberDao.update(m);
					}
				}
			}
		}

	}

	@Override
	public Long save(VMember vMember) {
		//System.out.println(vMember.getAddress());
		Member m = new Member();
		BeanUtils.copyProperties(vMember, m);
		if (vMember.getTid() != null) {
			MemberType type = new MemberType();
			type.setId(vMember.getTid());
			m.setMemberType(type);
		}
		if (vMember.getUid() != null) {
			User user = new User();
			user.setId(vMember.getUid());
			m.setUser(user);
		}
		if (vMember.getCtId() != null) {
			CustomerType ct = new CustomerType();
			ct.setId(vMember.getCtId());
			m.setCustomerType(ct);
		}
		
		if(m.getOpenDate()==null){
			m.setOpenDate(new Date());
		}
		
		if(vMember.getConsume()==null){
			m.setConsume(new Double(0));
		}else{
			m.setConsume(vMember.getConsume());
		}
		if(vMember.getPoint()==null){
			m.setPoint(0);
		}else {
			m.setPoint(vMember.getPoint());
		}
		//设置所属仓库
		if(vMember.getRid()!=null){
			m.setRepertory(new Repertory(vMember.getRid()));
		}
		m.setIsDeleted(false);
		memberDao.save(m);
		return m.getId();
	}

	@Override
	public void update(VMember vMember) {
		Member m = memberDao.get(Member.class, vMember.getId());
		vMember.setOpenDate(m.getOpenDate());
		BeanUtils.copyProperties(vMember, m);
		if (vMember.getTid() != null) {
			MemberType type = new MemberType();
			type.setId(vMember.getTid());
			m.setMemberType(type);
		}
		if(vMember.getMemberType()!=null){
			m.setMemberType(vMember.getMemberType());
		}
		if(vMember.getPoint()!=null){
			m.setPoint(vMember.getPoint());
		}
		if (vMember.getUid() != null) {
			User user = new User();
			user.setId(vMember.getUid());
			m.setUser(user);
		}
		if(vMember.getConsume()==null){
			m.setConsume(0d);
		}else {
			m.setConsume(vMember.getConsume());
		}
		
		if(vMember.getRid()!=null){
			m.setRepertory(new Repertory(vMember.getRid()));
		}

		m.setIsDeleted(false);
		memberDao.update(m);

	}

	@Override
	public Member getMemberById(Long id) {
		return memberDao.get(Member.class, id);
	}

	public VMember getMnameById(Long id){
		VMember vm = new VMember();
		try {
			String hql = " select name,mobile from hygl_member where id ="+id;
			List<Object[]> list = memberDao.findBySql(hql);
			for(Object[] o:list){
				for (int i = 0; i < o.length; i++) {
					vm.setName(o[i].toString());
					vm.setMobile(o[++i].toString());
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return vm; 
	}
	@Override
	public void findImp() {
	/*	HSSFRow row = null;
		HSSFCell cell = null;
		ArrayList list = new ArrayList();
		VMember info = null;
		

		try {
			//--获得文件上传路径
//			String fileName = request.getParameter("impfile");
//			System.out.println("filepath:"+fileName);
//			//--读取文件
//			File f = new File(fileName);
//			FileInputStream is = new FileInputStream(f);
		
		/*	InputStream is =null;
			FileItemFactory factory  = new DiskFileItemFactory();
			ServletFileUpload upload = new ServletFileUpload(factory);
			List filelist = upload.parseRequest(request);
			Iterator  iter = filelist.iterator();
			while(iter.hasNext()){
				FileItem item = (FileItem)iter.next();
				is =item.getInputStream();

			}
			
			
			//构建一个POIexcel处理组件
			POIFSFileSystem fs = new POIFSFileSystem(is);
			//--构建一个excel工作簿
			HSSFWorkbook wb = new HSSFWorkbook(fs);
			//--获取工作簿的信息
			HSSFSheet sheet = wb.getSheetAt(0);
			//--获取一共多少行
			int rowNum = sheet.getLastRowNum();
			int k = 0;
			
			System.out.println("fileRows:"+rowNum);
			for (int i = 1; i < rowNum; i++) {
				k = 0;
				info = new VMember();
				row = sheet.getRow(i);
				if (row == null)
					break;
				// --序号 （暂时未用）
		/*		cell = row.getCell((short) k++);
				if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
					System.out.println("xh:"+Double.toString(cell.getNumericCellValue()).trim());
				} else if (HSSFCell.CELL_TYPE_STRING == cell.getCellType()) {
					System.out.println("xh:"+cell.getCellType());
				} else {
					System.out.println("xhers:");
				}
				// --合作伙伴
				cell = row.getCell((short) k++);
				if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
					info.setHzhbmc(Double.toString(cell.getNumericCellValue()).trim());
				} else if (HSSFCell.CELL_TYPE_STRING == cell.getCellType()) {
					info.setHzhbmc(cell.getStringCellValue());
				} else {
					info.setHzhbmc("");
				}
				
				// --区域
				cell = row.getCell((short) k++);
				if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
					info.setQy(Double.toString(cell.getNumericCellValue()).trim());
				} else if (HSSFCell.CELL_TYPE_STRING == cell.getCellType()) {
					info.setQy(cell.getStringCellValue());
				} else {
					info.setQy("");
				}
				
				// --法定代表人
				cell = row.getCell((short) k++);
				if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
					info.setFrdb(Double.toString(cell.getNumericCellValue()).trim());
				} else if (HSSFCell.CELL_TYPE_STRING == cell.getCellType()) {
					info.setFrdb(cell.getStringCellValue());
				} else {
					info.setFrdb("");
				}
				
				// --联系电话
				cell = row.getCell((short) k++);
				if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
					info.setLxdh(Double.toString(cell.getNumericCellValue()).trim());
				} else if (HSSFCell.CELL_TYPE_STRING == cell.getCellType()) {
					info.setLxdh(cell.getStringCellValue());
				} else {
					info.setLxdh("");
				}
				
				// --备注
				cell = row.getCell((short) k++);
				if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
					info.setBz(Double.toString(cell.getNumericCellValue()).trim());
				} else if (HSSFCell.CELL_TYPE_STRING == cell.getCellType()) {
					info.setBz(cell.getStringCellValue());
				} else {
					info.setBz("");
				}*/
				
				
//				list.add(info);
//			}
//			this.saveImp(list);
//			is.close();
		/*} catch (Exception ex) {
			ex.printStackTrace();
		}*/
	
	}

	private void saveImp(ArrayList list) {
		// TODO Auto-generated method stub
		
	}

	@Override
	public List<Object[]> memberTotal(VMember model) {
		// TODO Auto-generated method stub
		List<Object[]> vsls = new ArrayList<Object[]>();
		String hql = "";
		
		String quali=" having 1=1 ";
		
		//积分
		if(model.getPointStart()!=null&&model.getPointEnd()!=null){
			quali+=" and sum(t.point)>="+model.getPointStart()+" and sum(t.point)<="+model.getPointEnd()+" ";
		}
		
		//金额
		if(model.getConsumeStart()!=null&&model.getConsumeEnd()!=null){
			 quali+=" and sum(t.consume)>="+model.getConsumeStart()+" and sum(t.consume)<="+model.getConsumeEnd()+" ";
		}
		
		try{
			if(model.getUid()!=null){
				if(model.getGroupby().trim().equals("sex")){//通过性别统计
					hql = "select t.sex,sum(t.consume),sum(t.point),count(t.id) from Member t join t.user u where u.user.id= "+model.getUid()+ " GROUP BY t.sex order by sum(consume) DESC ";
					//hql+=quali;
				}else if(model.getGroupby().trim().equals("repertory")){//通过仓库统计
					hql = "select t.storename,sum(t.consume),sum(t.point),count(t.id) from hygl_member t join qxgl_user u where t.USER_ID=u.ID and u.USER_ID= "+model.getUid()+ " GROUP BY t.storename "+quali+" order by sum(consume) DESC ";
//					hql+=quali;
				}else if(model.getGroupby().trim().equals("type")){//通过会员类型统计
					hql = "select mt.name,sum(t.consume),sum(t.point),count(t.id) from hygl_member t join qxgl_user u  join hygl_member_type mt where 1=1 and t.USER_ID=u.ID and t.MEMBER_TYPE_ID=mt.ID and u.USER_ID= "+model.getUid()+"  GROUP BY mt.name "+quali+" order by sum(consume) DESC ";
					
				}else{//默认通过仓库统计
					hql = "select t.storename,sum(t.consume),sum(t.point),count(t.id) from hygl_member t join qxgl_user u where t.USER_ID=u.ID and u.USER_ID= "+model.getUid()+ " GROUP BY t.storename "+quali+" order  by sum(consume) DESC ";
//					hql+=quali;
				}
				vsls = memberDao.findBySql(hql);
			}
		}catch(Exception e){
			e.printStackTrace();
		}		
		
		return vsls;	
	}

	@Override
	public JFreeChart getMemberTotalChart(List<Object[]> vsls, VMember model) {
		// TODO Auto-generated method stub
		JFreeChart chart = null;
		DefaultPieDataset  dataset = new DefaultPieDataset ();
		String chartTitle = "会员分布统计图";

		try {
			if(model.getOrder().trim().equals("consume")){//按照消费金额排序
				chartTitle = "会员消费金额分布统计图";
				for(Object[] objarr:vsls){
					dataset.setValue((String)objarr[0],Double.parseDouble(String.valueOf(objarr[1])));
				}
			}else if(model.getOrder().trim().equals("point")){//按照积分排序
				chartTitle = "会员积分分布统计图";
				for(Object[] objarr:vsls){
					dataset.setValue((String)objarr[0],Double.parseDouble(String.valueOf(objarr[2])));
				}
			}else if(model.getOrder().trim().equals("count")){//按照会员数排序
				chartTitle = "会员数量分布统计图";
				for(Object[] objarr:vsls){
					dataset.setValue((String)objarr[0],Double.parseDouble(String.valueOf(objarr[3])));
				}
			}else{
				for(Object[] objarr:vsls){
					dataset.setValue((String)objarr[0],Double.parseDouble(String.valueOf(objarr[1])));
				}
			}
			
			chart = ChartFactory.createPieChart3D(chartTitle,
			  dataset,
			  false,
			  false,
			  false);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return chart;
		
	}

}
